Import Pipeline Overview
Overview
This document describes the end-to-end import pipeline, from processing CSV files through validation, transformation, and reconciliation in BigQuery, to importing the final data into Firestore.
The pipeline begins with Cloud Scheduler triggering Import Manager to validate the CSV data. Once validation is complete, a Cloud Task is queued, which invokes Queue Services. Queue Services performs the import, using BigQuery for data transformation and reconciliation before writing the final records to Firestore.
Flow Overview
Full Flow
Stage Summary
Stage 1 — CSV Validation (Import Manager)
The Import Manager downloads the CSV file from a GCS bucket, parses it, and validates every row against predefined schema. Valid rows are enriched with lease and sub-customer data and written to a *_valid.csv file, which is uploaded to GCS. For fleet files, a processed copy of the original CSV is also created — empty lines are removed and the file is uploaded to GCS for use during termination. Validation results are logged per row, recording the VRM, VIN, and any validation errors.
If the import is running in persist mode, a cloud task is enqueued to trigger the next stage.
If running in validation-only mode (persist: false), the pipeline stops here.
Stage 2 — Load and Shape (Queue Services)
The enqueued cloud task makes an HTTP request to Queue Services. Queue Services verifies the required GCS files exist, loads the validated CSVs into BigQuery staging tables, and archives the source files in GCS.
Once loaded, the shape stage transforms the imported data into a standardised structure that matches how vehicle and policy records appear in ViSN. If benefits data is included, it is joined to the fleet data at this stage.
Stage 3 — Aggregate and Hash
Rows are grouped by VRM so that each vehicle appears as a single record. When multiple policy rows exist for the same VRM, they are combined under one vehicle record with multiple policies. The row with the latest start date provides the shared vehicle-level details (make, model, registration, customer, driver).
After aggregation, a _docHash is computed from the final grouped record. This hash represents the complete ViSN-ready state of the vehicle and is the basis for all downstream comparison.
Stage 4 — Reconcile and Delta
The aggregated imported state is merged with the current persisted state from existing Firestore data. If a vehicle already exists in ViSN, the imported policies are merged with the existing policies. Policies present in the existing record but missing from the latest import are added back as cancelled.
After reconciliation, the delta check keeps only the records where the final state has changed. Unchanged records are excluded from the import. If reimportAll is enabled, all records are kept regardless of whether changes are detected.
Stage 5 — Import to Firestore
The delta rows are streamed from BigQuery in batches. Each row is written to Firestore as a vehicle stock document. If the vehicle already has a stockId, the existing document is updated; otherwise a new document is created.
Sub-customer details are resolved using the customer account number from the import, matching against known sub-customers. Per-row results are logged with VRM, VIN, stock ID, and success or error status.
Stage 6 — Termination (Optional)
If shouldTerminate is enabled, termination runs after the main import. It compares the current active vehicles in ViSN (BigQuery) against the latest import scope (the raw fleet table). Vehicles that exist in Firestore but are not present in the latest fleet data are candidates for termination.
Termination candidates are written to a BigQuery table and then streamed to Firestore in batches. Each vehicle's _docHash, contractStatus is updated to TERMINATED.
After termination completes, log files are uploaded to GCS and a summary email is sent with the import results.
Outputs at Each Stage
| Stage | Output |
|---|---|
| CSV Validation | *_valid.csv, *_processed.csv, validation log file |
| Load | BigQuery staging tables (raw, valid) |
| Shape | BigQuery shaped table |
| Aggregate | BigQuery aggregated table + _docHash |
| Reconcile | BigQuery merged table |
| Delta | BigQuery delta table |
| Firestore Import | Firestore vehicle stock documents |
| Termination | Terminated vehicle updates in Firestore |
Status: Approved
Category: Protected
Authored By: Hadley on Jul 08, 2026